package com.sihai.ehr.service.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.Date;

import javax.annotation.Resource;

import org.hibernate.Query;
import org.hibernate.Session;
import org.springframework.stereotype.Service;

import com.sihai.core.DataAccessor;
import com.sihai.core.DynamicQuery;
import com.sihai.core.Limit;
import com.sihai.core.LimitedList;
import com.sihai.core.QueryCreator;
import com.sihai.core.Sorter;
import com.sihai.core.support.HibernateDataAccessor;
import com.sihai.ehr.model.biz.TrainAssess;
import com.sihai.ehr.model.biz.TrainAssessWeight;
import com.sihai.ehr.model.biz.TrainPlan;
import com.sihai.ehr.service.TrainAssessService;
import com.sihai.ehr.web.pojo.TrainAssessPojo;

/**
 * 培训评价服务接口实现类
 * 
 * @author henry
 * 
 */
@Service("trainAssessService")
public class TrainAssessServiceImpl implements TrainAssessService {
	@Resource
	DataAccessor dataAccessor;

	@Override
	public TrainAssess getTrainAssess(Long id) {
		if (id == null || id < 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.get(TrainAssess.class, id);
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：获取培训评价明细数据库发生异常】【异常说明：+"
					+ e.getMessage()
					+ "】【发生地点：TrainAssessServiceImpl.getTrainAssess;参数id=" + id
					+ " 】");
		}
	}
	
	@Override
	public TrainAssess getTrainAssessByPlanWeight(final Long planId, final String weightTitle,final int assessType,final Long workId) {
		if (planId == null || planId < 0) {
			throw new RuntimeException("【传入参数非法】");
		}
		try {
			return dataAccessor.findUnique(TrainAssess.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from TrainAssess t where 1 <> 2");
							dynamicQuery
									.append(" and t.trainPlan.id=" + planId);
							if (weightTitle != null && !weightTitle.isEmpty()) {
								dynamicQuery
										.append(" and t.trainAssessWeight.title='" + weightTitle + "'");
							}
							dynamicQuery.append(" and t.assessType=" + assessType);
							dynamicQuery.append(" and t.employeeinfoWork.id=" + workId);
							return dynamicQuery.build(session);
						}
					});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：获取培训评价明细数据库发生异常】【异常说明：+"
					+ e.getMessage()
					+ "】【发生地点：TrainAssessServiceImpl.getTrainAssessByPlanWeight;参数planId=" + planId
					+ " 】");
		}
	}

	@Override
	public void save(TrainAssess trainAssess) {
		if (trainAssess == null) {
			throw new RuntimeException("【trainAssess对象参数异常】");
		}
		try {
			dataAccessor.saveOrUpdate(trainAssess);
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：保培训评价对象到数据库发生异常】【异常说明："
					+ e.getMessage() + "】【发生地点：TrainAssessServiceImpl.save】");
		}
	}

	@Override
	public LimitedList<TrainAssess> getTrainAssesses(final Long planId,
			final Long assessWeightId, Limit limit, Sorter sorter) {
		if (planId == null || planId < 0) {
			throw new RuntimeException("【planId传入参数非法】");
		}
		try {
			return dataAccessor.findByQuery(TrainAssess.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"from TrainAssess t where 1 <> 2");
							dynamicQuery
									.append(" and t.trainPlan.id=" + planId);
							if (assessWeightId > 0) {
								dynamicQuery
										.append(" and t.trainAssessWeight.id="
												+ assessWeightId);
							}
							return dynamicQuery.build(session);
						}
					}, limit, sorter);
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取培训评价时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：TrainAssessServiceImpl.getTrainAssesses;参数planId="
							+ planId + ",assessWeightId=" + assessWeightId
							+ "】");
		}
	}
	
	@Override
	public Long getTrainAssessNumByWeight(final Long planId, final String weightTitle,final int assessType,final Long workId) {
		if (planId == null || planId < 0) {
			throw new RuntimeException("【planId传入参数非法】");
		}
		try {
			return dataAccessor.findUnique(Long.class,
					new QueryCreator() {

						@Override
						public Query createQuery(Session session) {
							DynamicQuery dynamicQuery = new DynamicQuery(
									"select count(t.id) from TrainAssess t where 1 <> 2");
							dynamicQuery
									.append(" and t.trainPlan.id=" + planId);
							if (weightTitle != null && !weightTitle.isEmpty()) {
								dynamicQuery
										.append(" and t.trainAssessWeight.title='" + weightTitle + "'");
							}
							dynamicQuery.append(" and t.assessType=" + assessType);
							dynamicQuery.append(" and t.employeeinfoWork.id<>" + workId);
							return dynamicQuery.build(session);
						}
					});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：获取培训评价时数据库发生异常】【异常说明：+"
							+ e.getMessage()
							+ "】【发生地点：TrainAssessServiceImpl.getTrainAssessNumByWeight;参数planId="
							+ planId + ",weightTitle=" + weightTitle
							+ "】");
		}
	}

	@Override
	public void deleteTrainAssess(final String ids) {
		if (ids == null || ids.isEmpty()) {
			throw new RuntimeException("【参数异常：ids为空】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {
				@Override
				public Query createQuery(Session session) {
					return session.createQuery(MessageFormat.format(
							"delete from TrainAssess t where t.id in ({0})",
							ids));
				}
			});
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：移出培训评价信息数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：TrainAssessServiceImpl.deleteTrainAssess】");
		}
	}

	@Override
	public void deleteTrainAssessByPlanId(final Long planId) {
		if (planId == null || planId < 0) {
			throw new RuntimeException("【参数异常：planId为空】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {
				@Override
				public Query createQuery(Session session) {
					return session.createQuery(MessageFormat.format(
							"delete from TrainAssess t where t.id={0}", planId));
				}
			});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：移出培训评价信息数据库发生异常】【异常说明："
							+ e.getMessage()
							+ "】【发生地点：TrainAssessServiceImpl.deleteTrainAssessByPlanId】");
		}
	}

	@Override
	public void deleteTrainAssessByPlanIds(final String planIds) {
		if (planIds == null || planIds.isEmpty()) {
			throw new RuntimeException("【参数异常：ids为空】");
		}
		try {
			dataAccessor.batchUpdate(new QueryCreator() {
				@Override
				public Query createQuery(Session session) {
					return session.createQuery(MessageFormat
							.format("delete from TrainAssess t where t.trainPlan.id in ({0})",
									planIds));
				}
			});
		} catch (Exception e) {
			throw new RuntimeException(
					"【异常原因：移出培训评价信息数据库发生异常】【异常说明："
							+ e.getMessage()
							+ "】【发生地点：TrainAssessServiceImpl.deleteTrainAssessByPlanIds】");
		}

	}

	@Override
	public LimitedList<TrainAssessPojo> getTrainAssessPojos(final Long planId,
			final String weightTitle, final int assessType, Limit limit,
			Sorter sorter) {
		// 检测参数
		LimitedList<TrainAssessPojo> assessPojos = new LimitedList<TrainAssessPojo>();
		try {
			Long weightId = dataAccessor.findUnique(Long.class,
					new QueryCreator() {
						@Override
						public Query createQuery(Session session) {
							return session
									.createQuery("select g.id from TrainAssessWeight g where g.title='"
											+ weightTitle
											+ "' and g.assessType="
											+ assessType);
						}
					});
			if (weightId == null) {
				weightId = 0L;
			}

			String strSQLString = "";
			if (assessType == 0) {
				if(weightTitle.equals("HR评价")){
					strSQLString = "select t.id,t.plan_id,t.weight_id,w.id as work_id,e.name ,w.work_no,t.assess_content,t.score,t.create_date "
							+ "from (select * from train_assess where weight_id="
							+ weightId
							+ " and assess_type=0 and plan_id="
							+ planId
							+ ") as t "
							+ " right outer join employeeinfo_work w  on t.work_id=w.id, employeeinfo_base e "
							+ " where w.base_id=e.id  and w.cancel<>1  and ("
							+ " w.duty_id in ( select duty_id from role as r,role_resource as s,resource as c "
							+ "where r.id=s.role_id and s.resource_id=c.id and c.name='" + weightTitle + "' ))";
					//根据计划编号获取组织结构路径
					TrainPlan mPlan = dataAccessor.get(TrainPlan.class, planId);
					if(mPlan != null){
						strSQLString += " and w.organization_id in (select id from organization where top_organization_id=" + mPlan.getOrganization().getId() + ")";
					}
				}else if(weightTitle.equals("教师自评")){
					strSQLString = "select t.id,t.plan_id,t.weight_id,w.id as work_id,e.name ,w.work_no,t.assess_content,t.score,t.create_date "
							+ "from (select * from train_assess where weight_id="
							+ weightId
							+ " and assess_type=0 and plan_id="
							+ planId
							+ ") as t "
							+ " right outer join employeeinfo_work w  on t.work_id=w.id, employeeinfo_base e "
							+ " where w.base_id=e.id  and w.cancel<>1 ";
					Long workId = dataAccessor.get(TrainPlan.class, planId)
							.getTrainLecturer().getEmployeeinfoWork().getId();
					if (workId != null && workId > 0) {
						strSQLString += " and w.id=" + workId;
					}
							
				}else{
					strSQLString = "select t.id,t.plan_id,t.weight_id,w.id as work_id,e.name ,w.work_no,t.assess_content,t.score,t.create_date "
						+ "from (select * from train_assess where weight_id="
						+ weightId
						+ " and assess_type=0 and plan_id="
						+ planId
						+ ") as t "
						+ " right outer join employeeinfo_work w  on t.work_id=w.id, employeeinfo_base e "
						+ " where w.base_id=e.id  and w.cancel<>1  and ("
						+ " w.duty_id in ( select duty_id from train_plan_duty_target "
						+ "where plan_id=" + planId + " ))";
				}
			} else {
				strSQLString = "select t.id,t.plan_id,t.weight_id,w.id as work_id,e.name ,w.work_no,t.assess_content,t.score,t.create_date "
						+ "from (select * from train_assess where weight_id="
						+ weightId
						+ " and assess_type=1 and plan_id="
						+ planId
						+ ") as t "
						+ " right outer join employeeinfo_work w  on t.work_id=w.id, employeeinfo_base e "
						+ " where w.base_id=e.id  and w.cancel<>1  and ("
						+ " w.id in ( select work_id from train_plan_employee_target "
						+ "where plan_id=" + planId + " ))";
			}

			if (limit != null) {
				strSQLString += " limit " + limit.getOffset() + ","
						+ limit.getCount();
			}

			Session vSession = ((HibernateDataAccessor) dataAccessor)
					.getHibernateTemplate().getSessionFactory()
					.getCurrentSession();
			Connection vConnection = vSession.connection();
			Statement vStatement = vConnection.createStatement();
			ResultSet rs = vStatement.executeQuery(strSQLString);
			while (rs.next()) {
//				if (rs.getLong("weight_id") != weightId) {
					TrainAssessPojo assessPojo = new TrainAssessPojo(
							rs.getLong("id"),
							planId, // rs.getLong("plan_id")
							rs.getLong("weight_id"), weightTitle,
							rs.getLong("work_id"), rs.getString("name"),
							rs.getString("work_no"),
							rs.getString("assess_content"),
							rs.getFloat("score"), rs.getDate("create_date"));
					assessPojos.add(assessPojo);
				// } else {
				// TrainAssessPojo assessPojo = new TrainAssessPojo(
				// rs.getLong("id"),
				// planId, // rs.getLong("plan_id")
				// 0L, weightTitle, rs.getLong("work_id"),
				// rs.getString("name"), rs.getString("work_no"), "",
				// 0F, null);
				// assessPojos.add(assessPojo);
				// }
			}
			rs.close();
			vStatement.close();
			vConnection.close();
			return assessPojos;
		} catch (Exception e) {
			throw new RuntimeException("【异常原因：查询培训评价信息POJO对象到数据库发生异常】【异常说明："
					+ e.getMessage()
					+ "】【发生地点：TrainAssessServiceImpl.getTrainAssessPojos】");
		}
	}

}
